package cm.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;


public class Excel2JSON {
	public static final String[] PNS_CJ={"xh","bh",null,"mz","mc","nd","width","height","cz","sl","gk","gs","bj"};
	public static final String[] PNS_FS={"xh",null,"gk","bh","mz","mc","cz","width","height","hu","sl","gs","bj"};
	public static final String[] PNS_JZ={"bh","mc","mz","nd","zz","cc","js"};
	public static final String PNKEY="bh";
	
	public static Gson gson =new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").setPrettyPrinting().create();;
	
	
	public HashMap getMapFromExcel(String fpath, String[] pns, String b360) throws IOException{
		FileInputStream fis = new FileInputStream(fpath);
		Workbook wb = new XSSFWorkbook(fis); 
		Sheet sheet = wb.getSheetAt(0);		
		int pos = 0;
		HashMap<String,HashMap<String,String>> rm = new HashMap<String,HashMap<String,String>>();
		for(Row row : sheet) {  
			if(row.getRowNum()<2){
				continue;
			}
			HashMap<String,String> mo = new HashMap<String,String>();
			pos++;
			for(int i=0; i<pns.length; i++){
				String pn = pns[i];
				Cell c = row.getCell(i);
				if(pn==null || c==null)
					continue;
				String cv="";
				switch(c.getCellType()){
					case Cell.CELL_TYPE_STRING:
						cv = c.getStringCellValue();
						break;
					case Cell.CELL_TYPE_NUMERIC:
						cv = c.getNumericCellValue()+"";
						break;
				}
				if(cv!=null && !cv.trim().equals("")){
					mo.put(pn, cv.trim());
				}
				System.out.println(pn+"["+pos+"-"+i+"]:"+cv);
			}
			if(b360!= null)
				mo.put("b360", b360);

			String kv = mo.get(PNKEY);
			if(kv!=null && !kv.trim().equals("")){
				rm.put(kv,mo);
			}
	    }  
		wb.close();
		fis.close();
		return rm;
	}
	
	public void e2j(String fpath,String[] pns, String b360) throws IOException{
		HashMap rm = getMapFromExcel(fpath,pns,b360);
		String jstr = gson.toJson(rm);
		rm.clear();
		int pos = fpath.lastIndexOf(".");
		String fjson = fpath.substring(0,pos)+".json";
		FileOutputStream os = new FileOutputStream(fjson);    
		BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(os, "UTF-8")); 
		writer.write("jQuery.axZm.mb_="+jstr);
		writer.close();  
		os.close();
	}
	public void e2jz(String fpath,String[] pns) throws IOException{
		HashMap rm = getMapFromExcel(fpath,pns,null);
		HashMap<String,HashMap<String,String>> m = new HashMap<String,HashMap<String,String>>();
		//剔除非法数据
		Iterator it=rm.keySet().iterator();      

        while(it.hasNext()){
            String str=(String)it.next(); 
            try{
            	Double dstr = Double.parseDouble(str);
            	
            	HashMap<String,String> mo = (HashMap<String,String>)rm.get(str);
            	String bh = "JZ"+dstr.intValue();
            	mo.put("bh", bh);
            	it.remove();  
            	m.put(bh, mo);
            }catch(Exception e){
            	it.remove();  
            }
        }   
		
		
		String jstr = gson.toJson(m);
		rm.clear();
		int pos = fpath.lastIndexOf(".");
		String fjson = fpath.substring(0,pos)+".json";
		FileOutputStream os = new FileOutputStream(fjson);    
		BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(os, "UTF-8")); 
		writer.write("jQuery.axZm.mb_="+jstr);
		writer.close();  
		os.close();
	}
	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		Excel2JSON ej1= new Excel2JSON();
		ej1.e2j("F:\\mb\\Git\\azutil\\azutil\\resource\\fs.xlsx",PNS_FS,"有");
		//ej1.e2jz("/Users/c4w/git/azutil/azutil/resource/jz.xlsx",PNS_JZ);
	}

}
